﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBAccessLibrary.Common;
using DBAccessLibrary.Interface;
using DBAccessLibrary.Model;
using DataAccess;
using System.Windows.Media;

namespace DBAccessLibrary
{
    internal abstract class DBGrammar 
    {
        protected readonly ChDescriptionXml _xml;
        protected ConnectionModel _connect;
        public DBGrammar(ConnectionModel connect)
        {
            _connect = connect;
            _xml = new ChDescriptionXml();
        }

        public string SelectAll(TableEntity table)
        {
            return string.Format("SELECT {0} FROM {1} ",
                               string.Join(",", table.FileNodes.OrderBy(o => o.Index).Select(o =>
                               {
                                   if (o.IsTrans)
                                   {
                                       if (string.IsNullOrEmpty(o.TransChName))
                                           return o.TransName;
                                       return o.TransName + " AS " + o.TransChName;
                                   }
                                   else
                                       return o.TransName;
                               })),
                               table.TransName);

        }

        /// <summary>
        /// 添加 tab 后自动生成的语句
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        protected string TabText(string text)
        {
            switch (text.ToUpper().Trim())
            {
                case "S":
                    return "SELECT * FROM";
                case "U":
                    return "UPDATE";
                case "D":
                    return "DELETE FROM";
                case "DT":
                    return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                default:
                    return string.Empty;
            }
        }

       
        /// <summary>
        /// 生成建表语句
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="targetType"> </param>
        /// <returns></returns>
        public string GetCreateInfo(DBType targetType,TableEntity entity)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(GetCreateTable(targetType, entity,true));
            sb.Append(";");
            sb.Append(Environment.NewLine + Environment.NewLine);

            if (targetType == DBType.DB2)
            {
                #region 添加外键
                if (entity.RelationNodes.Any())
                    sb.Append("--添加外键信息" + Environment.NewLine);
                foreach (RelationshipEntity relentity in entity.RelationNodes)
                {
                    sb.Append(string.Format("ALTER TABLE {0} " + Environment.NewLine +
                               " ADD CONSTRAINT \"{1}\" FOREIGN KEY " + Environment.NewLine +
                               " (\"{2}\") REFERENCES {3} (\"{4}\")" + Environment.NewLine +
                               " ON DELETE {5} " + Environment.NewLine +
                               " ON UPDATE {6} " + Environment.NewLine +
                               " ENFORCED ENABLE QUERY OPTIMIZATION "
                     ,
                     entity.TransName,       //表名
                     relentity.RefKeyName,   //外键名称
                     relentity.ColumnName,   //外键字段
                     relentity.RelationTableName,    //关系表名
                     relentity.RelationColumnName,   //关系字段名
                     relentity.IsCascadingDelete ? "CASCADE" : "NO ACTION",   //是否删除级联
                     relentity.IsCascadingUpdate ? "CASCADE" : "NO ACTION"    //是否更新级联
                     ));
                    sb.Append(";" + Environment.NewLine + Environment.NewLine);
                }
                #endregion

                #region 添加索引
                if (entity.IndexNodes.Any())
                    sb.Append("--添加索引信息" + Environment.NewLine);
                foreach (IndexDescriptionEntity key in entity.IndexNodes)
                {
                    //            CREATE UNIQUE INDEX "DB2ADMIN"."INDEX_1" ON "DB2ADMIN"."FUNCTIONLOG" 
                    //("ID" ASC,
                    // "IP" ASC)
                    //PCTFREE 10 MINPCTUSED 10

                    //COLLECT SAMPLED DETAILED STATISTICS 
                    //COMPRESS NO ALLOW REVERSE SCANS;

                    List<string> indexStr = (from IndexEntity indexEntity in key.ChildNodes 
                                             select string.Format("\"{0}\" {1}", indexEntity.ColumnName, indexEntity.Sequence)).ToList();
                    if (indexStr.Count > 0)
                    {
                        if (key.IsUnique)
                        {
                            //sb.Append(string.Format("CREATE UNIQUE INDEX {0} ON {1} " + Environment.NewLine +
                            //                             "({2}) " + Environment.NewLine +
                            //                             "PCTFREE 10 MINPCTUSED 10 " + Environment.NewLine +
                            //                             "COLLECT SAMPLED DETAILED STATISTICS " + Environment.NewLine +
                            //                             " COMPRESS NO ALLOW REVERSE SCANS",
                            //                             key.Key,      //索引名称
                            //                             entity.Name,               //表名
                            //                             string.Join("," + Environment.NewLine, indexStr.ToArray())));
                        }
                        else
                        {
                            sb.Append(string.Format("CREATE INDEX {0} ON {1} (" + Environment.NewLine +
                                                         "{2}" + Environment.NewLine +
                                                         ")" + Environment.NewLine +
                                                         " COMPRESS NO ALLOW REVERSE SCANS",
                                                         key.Name,      //索引名称
                                                         entity.Name,               //表名
                                                         string.Join("," + Environment.NewLine, indexStr.ToArray())));
                        }
                        sb.Append(";" + Environment.NewLine + Environment.NewLine);
                    }

                }

                #endregion
            }
            else if (targetType == DBType.SqlServer)
            {
                #region 添加外键
                if (entity.RelationNodes.Any())
                    sb.Append("--添加外键信息" + Environment.NewLine);
                foreach (RelationshipEntity relentity in entity.RelationNodes)
                {
                    sb.Append(string.Format("ALTER TABLE [{0}] WITH CHECK" + Environment.NewLine +
                               " ADD CONSTRAINT [{1}] FOREIGN KEY " + Environment.NewLine +
                               " ([{2}]) REFERENCES [{3}] ([{4}])" + Environment.NewLine +
                               " {5} {6} " + Environment.NewLine
                     ,
                     entity.TransName,       //表名
                     relentity.RefKeyName,   //外键名称
                     relentity.ColumnName,   //外键字段
                     relentity.RelationTableName,    //关系表名
                     relentity.RelationColumnName,   //关系字段名
                     relentity.IsCascadingDelete ? "ON DELETE CASCADE " + Environment.NewLine : "",   //是否删除级联
                     relentity.IsCascadingUpdate ? "ON UPDATE CASCADE " + Environment.NewLine : ""    //是否更新级联
                     ));
                    sb.Append(";" + Environment.NewLine + Environment.NewLine);
                }
                #endregion

                #region 添加索引
                if (entity.IndexNodes.Any())
                    sb.Append("--添加索引信息" + Environment.NewLine);
                foreach (IndexDescriptionEntity key in entity.IndexNodes)
                {
                    List<string> indexStr = (from IndexEntity indexEntity in key.ChildNodes 
                                             select string.Format("[{0}] {1}", indexEntity.ColumnName, indexEntity.Sequence)).ToList();

                    if (indexStr.Count > 0)
                    {
                        if (!key.IsUnique)
                        {
                            sb.Append(
                                string.Format("CREATE {0}NONCLUSTERED INDEX [{1}] ON [{2}] (" + Environment.NewLine +
                                              "{3}" + Environment.NewLine +
                                              ")" + Environment.NewLine +
                                              "WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) " +
                                              Environment.NewLine +
                                              "ON [PRIMARY]",
                                              key.IsUnique ? "UNIQUE " : "",
                                              key.Name, //索引名称
                                              entity.Name, //表名
                                              string.Join("," + Environment.NewLine, indexStr.ToArray())));
                            sb.Append(";" + Environment.NewLine + Environment.NewLine);
                        }
                    }

                }
                #endregion
            }
            else if (targetType == DBType.Oracle)
            {
                //              alter table CP_PATIENTSDISCHARGE
                //add constraint FK_PATIE_R_PATIE foreign key (PATIENTSCLINICALID, MEDICALCODE)
                //references CP_PATIENTSCLINICAL (PATIENTSCLINICALID, MEDICALCODE);
                #region 添加外键
                if (entity.RelationNodes.Any())
                    sb.Append("--添加外键信息" + Environment.NewLine);

                foreach (IGrouping<string, RelationshipEntity> key in entity.RelationNodes.GroupBy(o => o.RefKeyName))
                {
                    List<RelationshipEntity> reflist = key.ToList();
                    sb.Append(string.Format("ALTER TABLE {0} " + Environment.NewLine +
                               " ADD CONSTRAINT {1} FOREIGN KEY " + Environment.NewLine +
                               " ({2}) REFERENCES {3} ({4})" + Environment.NewLine +
                               " {5} {6} " + Environment.NewLine
                     ,
                     entity.TransName,       //表名
                     key.Key,   //外键名称
                     string.Join(",", reflist.Select(o => o.ColumnName).ToArray()),   //外键字段
                     reflist[0].RelationTableName,    //关系表名
                     string.Join(",", reflist.Select(o => o.RelationColumnName).ToArray()),   //关系字段名
                     reflist[0].IsCascadingDelete ? "ON DELETE CASCADE " + Environment.NewLine : "",   //是否删除级联
                     reflist[0].IsCascadingUpdate ? "ON UPDATE CASCADE " + Environment.NewLine : ""    //是否更新级联
                     ));
                    sb.Append(";" + Environment.NewLine + Environment.NewLine);
                }

                #endregion

                #region 添加索引
                //create index DATETIME on P_DIAGSENDLEECHDOM (OPERATEDATE)

                if (entity.IndexNodes.Any())
                    sb.Append("--添加索引信息" + Environment.NewLine);
                foreach (IndexDescriptionEntity key in entity.IndexNodes)
                {
                    List<string> indexStr = (from IndexEntity indexEntity in key.ChildNodes
                                             select string.Format("{0} {1}", indexEntity.ColumnName, indexEntity.Sequence)
                                             ).ToList();
                    if (indexStr.Count > 0)
                    {
                        if (!key.IsUnique)
                        {
                            sb.Append(string.Format("CREATE {0} INDEX {1} ON {2}(" + Environment.NewLine +
                                                    "{3}" + Environment.NewLine +
                                                    ")",
                                                    key.IsUnique ? "UNIQUE " : "",
                                                    key.Name, //索引名称
                                                    entity.Name, //表名
                                                    string.Join("," + Environment.NewLine, indexStr.ToArray())));
                            sb.Append(";" + Environment.NewLine + Environment.NewLine);
                        }
                    }

                }
                #endregion
            }
            return sb.ToString();
        }
        protected string GetCreateTable(DBType targetType, TableEntity entity,bool isAddPrimary)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format("CREATE TABLE {0} (", entity.TransName));
            string[] types = new[] { "VARCHAR", "CHAR", "VARCHAR2","NVARCHAR" };
            string[] inttypes = new[] { "DECIMAL", "NUMBER", "BLOB", "CLOB" };
            List<string> primarys = new List<string>();
            List<FieldEntity> list = entity.FileNodes.OrderBy(o => o.Index).ToList();
            for (int i = 0; i < list.Count; i++)
            {
                FieldEntity field = list[i];
                if (field == null)
                    continue;
                if (field.IsPrimaryKey)
                    primarys.Add(field.TransName);

                string name = field.TransName;
                string nullstring = field.IsNull ? "" : "NOT NULL";
                string chname = field.TransChName;

                if (types.Contains(field.TypeName.ToUpper()))
                {
                    sb.Append(string.Format("   {0}{1}{2}",
                        name.PadRight(30, ' '),
                        (field.TypeName + "(" + field.Length + ")").PadRight(20, ' '),
                        nullstring.PadLeft(10, ' ')));

                }
                else
                {
                    string length = string.Empty;
                    if (inttypes.Contains(field.TypeName.ToUpper()))
                    {
                        length = "(" + field.Length;
                        if (!string.IsNullOrEmpty(field.Scale) && field.Scale != "0")
                        {
                            length += "," + field.Scale;
                        }
                        length += ")";
                    }
                    sb.Append(string.Format("   {0}{1}{2}",
                                          name.PadRight(30, ' '),
                                          (field.TypeName + length).PadRight(20, ' '),
                                          nullstring.PadLeft(10, ' ')));
                }
                if (!string.IsNullOrEmpty(field.DefaultValue))
                {
                    if (targetType == DBType.DB2)
                    {
                        sb.Append(" WITH DEFAULT " + field.DefaultValue);
                    }
                    else
                    {
                        sb.Append(" DEFAULT " + field.DefaultValue);
                    }
                }
                //如果是最后一列 判断是否有主键。没有主键的情况不需要增加最后的 逗号
                bool isEnd = i == entity.FileNodes.Count() - 1;
                //增加注释
                if (string.IsNullOrEmpty(chname))
                {
                    if (isEnd && (!isAddPrimary || primarys.Count == 0))
                    {
                        continue;
                    }
                    sb.AppendLine(",");
                }
                else
                {
                    if (isEnd && (!isAddPrimary || primarys.Count == 0))
                    {
                        sb.AppendLine("          --" + chname);
                    }
                    else
                    {
                        sb.AppendLine(",         --" + chname);
                    }
                }
            }
            //增加主键
            if (isAddPrimary && primarys.Count > 0)
            {
                sb.AppendLine(string.Format("   primary key({0})",
                    string.Join(",", primarys.ToArray())
                    ));
            }
            sb.AppendLine(Environment.NewLine + ")");
            return sb.ToString();
        }

        /// <summary>
        /// 获取 从dataset 生成的insert语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ds"></param>
        /// <param name="dbTypeAction"></param>
        /// <param name="tableName"> </param>
        /// <returns></returns>
        public string GetDataSetToInsertSql(string sql, DataSet ds, Func<string, bool?> dbTypeAction, out string tableName)
        {
            tableName = string.Empty;
            if (ds == null || ds.Tables.Count == 0)
                return string.Empty;
            if (string.IsNullOrEmpty(sql))
                return string.Empty;

            //获取表名
            sql = sql.Replace(Environment.NewLine, "");
            string[] content = sql.Split(new[] {" "}, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < content.Length; i++)
            {
                if (content[i].ToLower() == "from")
                {
                    tableName = content[i + 1].Trim();
                    break;
                }
            }

            //判断是否有时间字段。因为DB2跟ORACLE的处理方式不同
            string dbType = string.Empty;
            if (ds.Tables[0].Columns.Cast<DataColumn>().Any(c => c.DataType == typeof (DateTime)))
            {
                bool? result = dbTypeAction("由于导出列中包含时间类型，请选择导出数据库的格式。\r\n（是：DB2 否：ORACLE 取消：SQLSERVER）");
                if (result == true)
                    dbType = "DB2";
                else if (result == false)
                    dbType = "ORACLE";
                else
                    dbType = "SQLSERVER";
            }
            if (dbType == string.Empty)
            {
                if (ds.Tables[0].Columns.Cast<DataColumn>().Any(c => c.DataType == typeof (bool)))
                {
                    bool? result = dbTypeAction("由于导出列中包含布尔类型，请选择导出数据库的格式。\r\n（是：DB2，ORACLE 否：SQLSERVER）");
                    if (result == true)
                        dbType = "DB2";
                    else if (result == false)
                        dbType = "SQLSERVER";
                }
            }

            StringBuilder sb = new StringBuilder();

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                List<string> columList = new List<string>();
                List<string> valueList = new List<string>();
                foreach (DataColumn column in ds.Tables[0].Columns)
                {
                    #region 列
                    if (column.ColumnName == "Dynamic_ID" ||
                        column.ColumnName == "ROWNUMBER")
                        continue;
                    columList.Add(column.ColumnName);
                    if (column.DataType == typeof (string))
                    {
                        if (row[column.ColumnName] == null)
                        {
                            valueList.Add("''");
                        }
                        else
                        {
                            valueList.Add("'" + row[column.ColumnName] + "'");
                        }
                    }
                    else if (column.DataType == typeof (bool))
                    {
                        if (row[column.ColumnName] == null)
                        {
                            valueList.Add("Null");
                        }
                        else
                        {
                            if (dbType == "DB2")
                            {
                                valueList.Add((bool) row[column.ColumnName] ? "1" : "0");
                            }
                            else
                            {
                                valueList.Add("'" + row[column.ColumnName] + "'");
                            }
                        }
                    }
                    else if (column.DataType == typeof (DateTime))
                    {
                        if (row[column.ColumnName] == null ||
                            string.IsNullOrEmpty(row[column.ColumnName].ToString()))
                        {
                            valueList.Add("Null");
                        }
                        else
                        {
                            DateTime dt = Convert.ToDateTime(row[column.ColumnName].ToString());
                            if (dbType == "DB2")
                            {
                                valueList.Add(string.Format("timestamp('{0}')",
                                                            dt.ToString("yyyy-MM-dd-HH.mm.ss")));
                            }
                            else if (dbType == "ORACLE")
                            {
                                valueList.Add(string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')",
                                                            dt.ToString("yyyy-MM-dd HH:mm:ss")));
                            }
                            else
                            {
                                valueList.Add("'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                            }
                        }
                    }

                    else
                    {
                        if (row[column.ColumnName] == null || row[column.ColumnName].ToString() == string.Empty)
                        {
                            valueList.Add("0");
                        }
                        else
                        {
                            valueList.Add(row[column.ColumnName].ToString());
                        }
                    }
                    #endregion
                }
                sb.AppendLine(string.Format("insert into {0}({1}) values({2});",
                                            tableName,
                                            string.Join(",", columList.ToArray()),
                                            string.Join(",", valueList.ToArray())
                                  ));

            }
            return sb.ToString();
        }

        /// <summary>
        /// 提交表结构修改
        /// </summary>
        /// <param name="newList"></param>
        public virtual void SaveChange(TableEntity table, IList<FieldEntity> newList, Action<string, Color> WriteLog, Func<string, bool> showMsg)
        {
            try
            {
                List<FieldEntity> removeList = new List<FieldEntity>();
                List<FieldEntity> addList = new List<FieldEntity>();
                bool isPrimary = false;
                foreach (FieldEntity entity in newList)
                {
                    //删除
                    if (entity.Status == EditStatus.Delete)
                    {
                        foreach (string str in entity.RemoveColumnSql)
                        {
                            WriteLog(str, Colors.Black);
                            DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                        }
                        removeList.Add(entity);
                    }
                    //新增
                    else if (entity.Status == EditStatus.Add)
                    {
                        string sql = entity.AddColumnSql;
                        WriteLog(sql, Colors.Black);
                        DBOperatorFactory.GetOperator.ExecNonQuery(sql, SysCache.DBConnection, null, TransactionType.Transaction);
                        addList.Add(entity);
                    }
                    //编辑
                    else if (entity.Status == EditStatus.Modify)
                    {
                        foreach (string str in DBOperatorFactory.GetGrammar(SysCache.DBConnection).EditColumn(entity))
                        {
                            WriteLog(str, Colors.Black);
                            DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                        }
                    }

                    if (!isPrimary && entity.IsChange(o => o.IsPrimaryKey))
                    {
                        isPrimary = true;
                    }

                    //更新字典描述表
                    DBOperatorFactory.GetOperator.SaveDescription(table, entity, string.Empty, false, TransactionType.Transaction);
                }

                //主键
                if (isPrimary)
                {

                    foreach (string str in table.AddPrimarySql)
                    {
                        WriteLog(str, Colors.Black);
                        DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                    }
                }
                DBOperatorFactory.GetOperator.CommitTransaction(SysCache.DBConnection);
                table.Commit();
                //删除
                removeList.ForEach(o =>
                {
                    newList.Remove(o);
                    table.Remove(o);
                });
                //新增
                addList.ForEach(o => {
                    o.Status = EditStatus.UnChange;
                    table.Add(o); 
                });
                foreach (FieldEntity entity in removeList)
                {
                    newList.Remove(entity);
                }
                WriteLog("提交成功", Colors.Blue);
            }
            catch (Exception ex)
            {
                DBOperatorFactory.GetOperator.RollbackTransaction(SysCache.DBConnection);
                table.RollBack();
                WriteLog("回滚成功.异常详细:"+ex.Message, Colors.Red);
                throw;
            }
        }

        /// <summary>
        /// 提交数据修改
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Dictionary<string,Dictionary<string,object>> SubmitDataChange(DataSet ds, string tableName,string where)
        {
            if (ds == null || ds.GetChanges() == null)
                throw new Exception("没有可修改的数据");
            Dictionary<string, Dictionary<string, object>> result = new Dictionary<string, Dictionary<string, object>>();
            DataTable dt = ds.Tables[0].GetChanges();
            foreach (DataRow row in dt.Rows)
            {
                switch (row.RowState)
                {
                    case DataRowState.Added:
                        {
                            List<string> columns = new List<string>();
                            Dictionary<string, object> dic = new Dictionary<string, object>();
                            foreach (DataColumn column in dt.Columns)
                            {
                                if (column.ColumnName == "ROWNUMBER")
                                    continue;
                                columns.Add(column.ColumnName);
                                //列被改变
                                dic.Add("@" + column.ColumnName, row[column]);

                            }
                            if (dic.Count > 0)
                            {
                                result.Add(string.Format("INSERT INTO {0}({1}) VALUES({2})",
                                    tableName,
                                    string.Join(",", columns.ToArray()),
                                    string.Join(",", dic.Keys.ToArray())),dic);
                            }
                        }
                        break;
                    case DataRowState.Modified:
                        {
                            List<string> list = new List<string>();
                            Dictionary<string, object> dic = new Dictionary<string, object>();
                            foreach (DataColumn column in dt.Columns)
                            {
                                if (column.ColumnName == "ROWNUMBER")
                                    continue;
                                if (!row[column, DataRowVersion.Current].Equals(row[column, DataRowVersion.Original]))
                                {
                                    list.Add(column.ColumnName + "=@" + column.ColumnName);
                                    //列被改变
                                    dic.Add("@" + column.ColumnName, row[column]);
                                }
                            }
                            if (list.Count > 0)
                            {
                                result.Add(GetRowNumUpdate(tableName,
                                    where, 
                                    string.Join(",",list.ToArray()), 
                                    row["ROWNUMBER"].ToString()), dic);
                            }
                        }
                        break;
                    case DataRowState.Deleted:
                        {
                            result.Add(GetRowNumDelete(tableName,where,row["ROWNUMBER", DataRowVersion.Original].ToString()),null);
                        }
                        break;
                }
            }
            return result;
        }

        /// <summary>
        /// 判断是否是存储过程 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="procName"></param>
        /// <param name="paramlist"></param>
        /// <returns></returns>
        public bool GetExecProcInfo(string sql, out string procName, out List<DBParam> paramlist)
        {
            //int end = 0;
            string paramStr =string.Empty;
            procName = GetProcName(sql, out paramStr);
            paramlist = null;

            if (string.IsNullOrEmpty(procName))
                return false;
            string pname = procName.Trim();
            //不区分大小写
            ProcEntity entity = SysCache.ProcList.FirstOrDefault(o => o.Name.Equals(pname, StringComparison.CurrentCultureIgnoreCase));
            if (entity == null)
            {
                throw new Exception(string.Format("存储过程[{0}]不存在", procName));
            }
       
            string[] paraArray = paramStr.ToFilterKeyword(",").ToArray();
            if (paraArray.Count() > 0)
            {
                paramlist = new List<DBParam>();

                #region 自定义存储过程
                int count = entity.ChildNodes.Count;
                if (paraArray.Length < count)
                    throw new Exception("参数数据不对应,实际参数应该是:" + count);
                for (int i = 0; i < entity.ChildNodes.Count; i++)
                {
                    ProcParamEntity param = entity.ChildNodes[i] as ProcParamEntity;
                    if (param == null)
                        continue;

                    switch (param.Status)
                    {
                        case "IN":
                            {
                                paramlist.Add(new DBParam
                                {
                                    Name = param.Name,
                                    Value = ConverProcTypeValue(paraArray[i], param.TypeName),
                                    ParamType = 1,
                                    TypeName = param.TypeName
                                });
                            }
                            break;
                        case "OUT":
                            {
                                paramlist.Add(new DBParam
                                {
                                    Name = param.Name,
                                    ParamType = 2,
                                    TypeName = param.TypeName,
                                    Value = "?"
                                });
                            }
                            break;
                        case "INOUT":
                            {
                                paramlist.Add(new DBParam
                                {
                                    Name = param.Name,
                                    Value = ConverProcTypeValue(paraArray[i], param.TypeName),
                                    ParamType = 3,
                                    TypeName = param.TypeName
                                });
                            }
                            break;
                        default:
                            {
                                paramlist.Add(new DBParam
                                {
                                    Name = param.Name,
                                    Value = ConverProcTypeValue(paraArray[i], param.TypeName),
                                    ParamType = 6,
                                    TypeName = param.TypeName
                                });
                            }
                            break;
                    }
                }
                #endregion
            }
            return true;
        }
        //获取存储过程的名字
        protected virtual string GetProcName(string sql,out string paramStr)
        {
            paramStr = string.Empty;
            if (sql.StartsWith("EXEC ", StringComparison.CurrentCultureIgnoreCase))
            {
                int start = sql.IndexOf("EXEC ", StringComparison.CurrentCultureIgnoreCase) + 5;
                int end = sql.IndexOf("(", System.StringComparison.Ordinal);
                paramStr = sql.Substring(end + 1, sql.LastIndexOf(")", System.StringComparison.Ordinal) - end - 1);
                return sql.Substring(start, end - start);
            }
            return string.Empty;
        }
        protected virtual object ConverProcTypeValue(string value, string type)
        {
            switch (type.ToLower())
            {
                case "varchar":
                case "varchar2":
                case "nvarchar":
                case "char":
                case "text":
                    return value.Trim('\'');
                case "decimal":
                case "money":
                    return Convert.ToDecimal(value);
                case "double":
                case "number":
                    return Convert.ToDouble(value);
                case "Float":
                    return float.Parse(value);
                case "integer":
                case "int32":
                case "int16":
                    return Convert.ToInt32(value);
                case "date":
                case "time":
                case "timestamp":
                case "datetime":
                case "dbtimestamp":
                    return Convert.ToDateTime(value.Trim('\''));
            }
            return null;
        }

        protected virtual string GetRowNumUpdate(string tableName, string where, string column, string rownumber)
        {
            return tableName;
        }
        protected virtual string GetRowNumDelete(string tableName, string where, string rownumber)
        { return tableName; }


    }
}
